﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace DLL
{
    public class DARepresentative
    {
        // Chuỗi kết nối
        string strConnection;

        // SqlConnection
        SqlConnection sqlConn = null;
        private StringBuilder sbCommand = new StringBuilder(String.Empty);

        // Ten bang
        private const string c_table = "representative";

        // Ten cot cua bang Chu Dau Tu
        private const string c_id = "representative_id";
        private const string c_investor_id = "investor_id";
        private const string c_first_name = "first_name";

        private const string c_last_name = "last_name";
        private const string c_address_MQH = "address_MQH";
        private const string c_phone_INT = "phone_INT";
        private const string c_caracter = "caracter";
        private const string c_position = "position";

        public DARepresentative(string strConnection)
        {
            this.strConnection = strConnection;
        }

        private string BuildInsertString(BLL.BORepresentative investor)
        {
            sbCommand = new StringBuilder(String.Empty);
            sbCommand.AppendFormat("INSERT INTO {0}", c_table).AppendLine();
            sbCommand.AppendFormat("({0},", c_investor_id).AppendLine();
            sbCommand.AppendFormat("{0},", c_first_name).AppendLine();
            sbCommand.AppendFormat("{0},", c_last_name).AppendLine();
            sbCommand.AppendFormat("{0},", c_address_MQH).AppendLine();
            sbCommand.AppendFormat("{0},", c_phone_INT).AppendLine();
            sbCommand.AppendFormat("{0},", c_caracter).AppendLine();
            sbCommand.AppendFormat("{0})", c_position).AppendLine();
            sbCommand.AppendLine("VALUES (");
            sbCommand.AppendFormat("N'{0}',", investor.Ma_chu_DT).AppendLine();
            sbCommand.AppendFormat("N'{0}',", investor.Ho_MQH).AppendLine();
            sbCommand.AppendFormat("N'{0}',", investor.Ten_MQH).AppendLine();
            sbCommand.AppendFormat("N'{0}',", investor.Dia_chi).AppendLine();
            sbCommand.AppendFormat("N'{0}',", investor.Dien_thoai).AppendLine();
            sbCommand.AppendFormat("N'{0}',", investor.Tinh_cach).AppendLine();
            sbCommand.AppendFormat("N'{0}')", investor.Vi_tri).AppendLine();

            return sbCommand.ToString();
        }

        private string BuildUpdateString(BLL.BORepresentative representative)
        {
            sbCommand = new StringBuilder(String.Empty);
            sbCommand.AppendFormat("UPDATE {0}", c_table).AppendLine();
            sbCommand.AppendFormat("SET {0} = N'{1}',",c_first_name, representative.Ho_MQH).AppendLine();
            sbCommand.AppendFormat("{0} = N'{1}',", c_last_name, representative.Ten_MQH).AppendLine();
            sbCommand.AppendFormat("{0} = N'{1}',", c_address_MQH, representative.Dia_chi).AppendLine();
            sbCommand.AppendFormat("{0} = N'{1}',", c_phone_INT, representative.Dien_thoai).AppendLine();
            sbCommand.AppendFormat("{0} = N'{1}',", c_caracter, representative.Tinh_cach).AppendLine();
            sbCommand.AppendFormat("{0} = N'{1}'", c_position, representative.Vi_tri).AppendLine();

            sbCommand.AppendFormat("WHERE {0} =  N'{1}'", c_id, representative.Ma_moi_QH).AppendLine();

            return sbCommand.ToString();
        }

        private void OpenConnection()
        {
            sqlConn = new SqlConnection(strConnection);
            sqlConn.Open();
        }

        private void CloseConnection()
        {
            sqlConn.Close();
        }

        public bool CreateMQH(BLL.BORepresentative investor, string strConnection)
        {

            string strINSERT = this.BuildInsertString(investor);
            SqlConnection sqlConnection = new SqlConnection(strConnection);
            sqlConnection.Open();
            SqlCommand sqlCommand = new SqlCommand(strINSERT, sqlConnection);

            // execute Query
            sqlCommand.ExecuteNonQuery();
            sqlConnection.Close();
            
            return true;
        }

        public DataSet GetThongTinMQH(int investor_id)
        {
            DataSet ds = new DataSet();
            this.OpenConnection();
            sbCommand = new StringBuilder(String.Empty);
            sbCommand.AppendFormat("SELECT  * FROM {0} MQH", c_table).AppendLine();
            sbCommand.AppendFormat("WHERE MQH.investor_id =N'{0}'", investor_id).AppendLine();
            SqlCommand cmd = new SqlCommand(sbCommand.ToString(), sqlConn);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(ds);
            this.CloseConnection();
            return ds;
        }

        public void UpdateMQH(BLL.BORepresentative investor, string strConnection)
        {

            string strUpdate = this.BuildUpdateString(investor);


            SqlConnection sqlConnection = new SqlConnection(strConnection);
            sqlConnection.Open();
            SqlCommand sqlCommand = new SqlCommand(strUpdate, sqlConnection);

            // execute Query
            sqlCommand.ExecuteNonQuery();
            sqlConnection.Close();

        }
    }
}
